package com.skycode7.dao;

import com.skycode7.bean.NewsBean;
import com.skycode7.bean.NoticeBean;
import com.skycode7.util.DBconnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NoticeDao {
    DBconnection db = new DBconnection();
    public int add(NoticeBean notice) {

        Connection conn = db.getConn();//conn代表mysql连接
        String sql = "insert into notice values(null,?,?,?,?)";// 使用jdbc预处理对象添加
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            //将新闻数据对象中的数据依次绑定到sql命令中吗，
            ps.setString(1, notice.getNoticeTitle());
            ps.setString(2, notice.getNoticeContent());
            ps.setString(3, notice.getNoticeTime());
            ps.setString(4, notice.getAdminName());
            return ps.executeUpdate();//执行sql命令 完成数据插入
        } catch (SQLException e) {
            System.out.println("插入news表异常");
        }
        return 0;//如果添加异常 返回0
    }
    public int delete(int noticeId) {
        Connection conn = db.getConn();
        String sql = "delete from notice where NoticeId = ?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, noticeId);
            return ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("删除notice表异常");
        }
        return 0;//如果添加异常 返回0
    }
    public int update(NoticeBean notice) {
        Connection conn = db.getConn();//conn代表mysql连接
        String sql = "update notice set NoticeTitle=?,NoticeContent=?,NoticeTime=?,AdminName=? where NoticeId=?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            //将新闻数据对象中的数据依次绑定到sql命令中
            ps.setString(1, notice.getNoticeTitle() );
            ps.setString(2, notice.getNoticeContent());
            ps.setString(3, notice.getNoticeTime());
            ps.setString(4, notice.getAdminName());
            ps.setInt(5, notice.getNoticeID());
            return ps.executeUpdate();//执行sql命令 完成数据插入
        } catch (SQLException e) {
            System.out.println("更新news表异常");
        }
        return 0;//如果添加异常 返回0
    }
    public List<NoticeBean> getList(int page) {
        Connection conn = db.getConn();
        String sql = "select * from notice limit ?,?";//第一个开始的行数 第二个是查询显示的条数
        List<NoticeBean> list = new ArrayList<>();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);//固定一页显示10条数据
            ps.setInt(1, (page - 1) * 10);//page为1时行数是0 page为2时 10行
            ps.setInt(2, 10);//10表示显示10条数据
            ResultSet rs = ps.executeQuery();//rs是数据查询结果集 全部是字符串
            while (rs.next()) {
                NoticeBean notice = new NoticeBean();//每轮循环生命一个新闻对象
                notice.setNoticeID(rs.getInt("NoticeId"));//rs获取该行新闻ID 通过新闻对象封装方法设置新闻值
                notice.setNoticeTitle(rs.getString("NoticeTitle"));
                notice.setNoticeContent(rs.getString("NoticeContent"));
                notice.setNoticeTime(rs.getString("NoticeTime"));
                notice.setAdminName(rs.getString("AdminName"));
                list.add(notice);//将设置好的新闻对象放入list中
            }
        } catch (SQLException e) {
            System.out.println("查询news表异常");
        }
        return list;//异常情况返回空notice
    }
}